<?php

include 'ConnectDB.php';
include 'graph.php';
define('FPDF_FONTPATH', './fpdf/font/');
require_once ("fpdf/fpdf.php");
$connect = new ConnectDB();
$connect->connect();

class dso {

    function gettotalsell($connect) {

        $query = "select cs.salesrep_c,DAY(date_s_c),MONTH(date_s_c),cs.sellingdepartmentbranch_c,cs.sellingdivision_c,cs.netamount_c 
			from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
				where YEAR(date_s_c) = YEAR(NOW()) order by cs.sellingdivision_c";
        $result = $connect->getdata($query);
        return $result;
    }

    function sell($connect) {
        $query = " select DAY(date_s_c) as d,MONTH(date_s_c) as m,sum(netamount_c) as days from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
		where YEAR(date_s_c) = YEAR(NOW()) GROUP BY DAY(date_s_c),MONTH(date_s_c);";
        $result = $connect->getdata($query);
        return $result;
    }

    function total($connect) {
        $query = " select MONTH(date_s_c) as m,sum(netamount_c) as days from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
		where YEAR(date_s_c) = YEAR(NOW()) GROUP BY cs.sellingdepartmentbranch_c,MONTH(date_s_c)";
        $result = $connect->getdata($query);
        return $result;
    }
     function ttbydivision($connect) {
       
        $query = "select cs.sellingdivision_c,DAY(date_s_c),MONTH(date_s_c) as m,sum(netamount_c) as days from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
		where YEAR(date_s_c) = YEAR(NOW()) GROUP BY cs.sellingdivision_c,MONTH(date_s_c),DAY(date_s_c) ORDER BY MONTH(date_s_c)";
        $result = $connect->getdata($query);
        return $result;
    }
      function ttbydepartment($connect) {
        $query = "select cs.sellingdepartmentbranch_c,DAY(date_s_c),MONTH(date_s_c) as m,sum(netamount_c) as days from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
		where YEAR(date_s_c) = YEAR(NOW()) GROUP BY cs.sellingdepartmentbranch_c,MONTH(date_s_c),DAY(date_s_c) ORDER BY MONTH(date_s_c)";
      
        $result = $connect->getdata($query);
        return $result;
    }
    function totalall($connect) {
        $query = " select MONTH(date_s_c) as m,sum(netamount_c) as days from sell_sellout_cstm as cs  join sell_sellout as sell  on sell.id = cs.id_c
		where YEAR(date_s_c) = YEAR(NOW()) GROUP BY MONTH(date_s_c)";
        $result = $connect->getdata($query);
        return $result;
    }

}

Class Pdf extends FPDF {

    function dsopdf($total, $sell, $all, $ttall,$dep,$div) {
        for ($month = 0; $month <= 12; $month++) {//วนลูปเพื่อออกรายงานของทั้งปี
            $head = array("LOCATION NAME", "LOCATION NO.", "TERRITORY", "SALES REP", "DIVISION", "DEPARTMENT");//หัวกระดาษ
            $ss = array(18, 18, 18, 18, 18, 18);
            for ($i = 0; $i < count($head); $i++) {
                $this->Cell($ss[$i], 5, iconv('UTF-8', 'TIS-620', $head[$i]), 1, 0, 'C');
            }$year = date('Y');
            $this->SetXY(50, 5);
            $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', date("F", mktime(0, 0, 0, $month, 1, $year))), 1, 0, 'C');
            $this->Ln();
            $fdom = date("w", mktime(0, 0, 0, $month, 1, $year));
            $ct = 0;
            $this->SetXY(118, 5);
            for ($row = 1; $row < 7; $row++) {
                for ($week = 1; $week < 8; $week++) {
                    $ct++;
                    $value = mktime(0, 0, 0, $month, $ct - $fdom, $year);
                    if (date("m", $value) == $month) {
                        $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', date("D", $value)), 1, 0, 'C');
                    }
                }
            }
            $this->Ln();
            $this->SetX(118);
            $fdom = date("w", mktime(0, 0, 0, $month, 1, $year));
            $ct = 0;
            for ($row = 1; $row < 7; $row++) {
                for ($week = 1; $week < 8; $week++) {
                    $ct++;
                    $value = mktime(0, 0, 0, $month, $ct - $fdom, $year);
                    if (date("m", $value) == $month) {
                        $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', date("d", $value)), 1, 0, 'C');
                    }
                }
            } $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', 'total'), 1, 0, 'C');
            $this->Ln();
            foreach ($total as $day => $values) {
                if ($values['MONTH(date_s_c)'] == $month) {
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', ''), 1, 0, 'C');
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', ''), 1, 0, 'C');
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', ''), 1, 0, 'C');
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', $values['salesrep_c']), 1, 0, 'C');
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', $values['sellingdivision_c']), 1, 0, 'C');
                    $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', $values['sellingdepartmentbranch_c']), 1, 0, 'C');
                    $key = $day + 1;
                    if($key <= count($values)+1){
                        if ($values['sellingdepartmentbranch_c'] != $total[$key]['sellingdepartmentbranch_c']) {
                            $this->Ln();
                           // $this->SetX($X);
                            $this->Cell(108, 5, iconv('UTF-8', 'TIS-620', 'SUBTOTAL DEPARTMENT  '.$values['sellingdivision_c']), 1, 0, 'C');
                        if ($values['sellingdivision_c'] != $total[$key]['sellingdivision_c']) {
                            $this->Ln();
                            $this->Cell(108, 5, iconv('UTF-8', 'TIS-620', 'SUBTOTAL DIVISION  '.$values['sellingdepartmentbranch_c']), 1, 0, 'C');
                        }
                        }
                    }
                    if ($values['DAY(date_s_c)'] != 1) {
                        $X = $values['DAY(date_s_c)'] * 5 + 113;
                        $this->SetX($X);
                    }
                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($values['netamount_c'], 0)), 1, 0, 'C'); //ใช้วนลูปเพื่อเขียนค่ายอดขายในแต่ละวัน
                    if($key <= count($values)+1){
                        if ($values['sellingdepartmentbranch_c'] != $total[$key]['sellingdepartmentbranch_c']) {
                            $this->Ln();
                          if($day <= count($dep)+1){
                            $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', $dep[$day]['days']), 1, 0, 'C');
                          }
                        if ($values['sellingdivision_c'] != $total[$key]['sellingdivision_c']) {
                            $this->Ln();
                              if($day <= count($div)+1){
                            $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', $div[$day]['days']), 1, 0, 'C');
                              }
                        }
                        }
                    }
//                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($dep[$day]['days'], 0)), 1, 0, 'C');
//                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($div[$day]['days'], 0)), 1, 0, 'C');
                    $this->Ln();
                }
            }

            $this->Cell(18, 5, iconv('UTF-8', 'TIS-620', 'TOTAL'), 1, 0, 'C'); //ใช้วนลูปหายอดขายราายวันของพนักงานทั้งหมด
            foreach ($sell as $sells) {
                if ($sells['m'] == $month) {
                    if ($sells['d'] != 1) {
                        $X = $sells['d'] * 5 + 113;
                        $this->SetX($X);
                    }
                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($sells['days'], 0)), 1, 0, 'C');
                }
            }
            $this->SetY(15);
            foreach ($all as $alls) {
                if ($alls['m'] == $month) {
                    $info = cal_days_in_month(CAL_GREGORIAN, $alls['m'], $year); //นับจำนวนวันในแต่ละเดือน
                    switch ($info) {
                        case 28;
                            $this->SetX(253);
                            break;
                        case 29;
                            $this->SetX(258);
                            break;
                        case 30;
                            $this->SetX(268);
                            break;
                        case 31;
                            $this->SetX(273);
                            break;
                    }

                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($alls['days'], 0)), 1, 0, 'C');//ค่าที่อยู่ข้างหลังสุด

                    $this->Ln();
                }
            }

            foreach ($ttall as $ttalls) {
                if ($ttalls['m'] == $month) {
                    $info = cal_days_in_month(CAL_GREGORIAN, $ttalls['m'], $year);
                    switch ($info) {
                        case 28;
                            $this->SetX(253);
                            break;
                        case 29;
                            $this->SetX(258);
                            break;
                        case 30;
                            $this->SetX(268);
                            break;
                        case 31;
                            $this->SetX(273);
                            break;
                    }

                    $this->Cell(5, 5, iconv('UTF-8', 'TIS-620', number_format($ttalls['days'], 0)), 1, 0, 'C');//ค่าที่อยู่ข้างหลังสุด
                    $this->Ln();
                }
            }
            $this->AddFont('tahoma', '', 'tahoma.php');
            $this->SetFont('tahoma', '', 5);
            $this->AddPage('L');
        }
    }

}

$dso = new dso();
$pdf = new Pdf();
$value = $dso->gettotalsell($connect);
$sell = $dso->sell($connect);
$all = $dso->total($connect);
$ttall = $dso->totalall($connect);
$dep = $dso->ttbydepartment($connect);
$div = $dso->ttbydivision($connect);
$pdf->dsopdf($value, $sell, $all, $ttall,$dep,$div);
$pdf->Output('pdf/daily_sellout.pdf');
?>
